const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 验证table_c14修复结果');
console.log('=========================');

try {
  // 1. 验证表结构
  console.log('\n📋 当前table_c14表结构:');
  const tableInfo = db.prepare("PRAGMA table_info(table_c14)").all();
  const dataFields = tableInfo.filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  console.log(`  数据字段数量: ${dataFields.length}`);
  dataFields.forEach((col, index) => {
    console.log(`  ${index + 1}. ${col.name} (${col.type})`);
  });
  
  // 2. 验证字段元数据
  console.log('\n📊 字段元数据:');
  const fieldMetadata = db.prepare(`
    SELECT field_sequence, field_name, field_column, data_type, data_source, example_description
    FROM parameter_field_metadata 
    WHERE table_name = 'table_c14' 
    ORDER BY field_sequence
  `).all();
  
  fieldMetadata.forEach(field => {
    console.log(`  ${field.field_sequence}. ${field.field_name} [${field.data_source}] - ${field.example_description}`);
  });
  
  // 3. 与Excel定义对比
  console.log('\n✅ 与Excel定义对比:');
  const expectedFields = [
    {name: '规格', source: 'E'},
    {name: '型号', source: 'P'}, 
    {name: '制造厂家', source: 'P'},
    {name: '供货厂家', source: 'P'},
    {name: '供货类型', source: 'E'},
    {name: '用途', source: 'E'}
  ];
  
  let allMatch = true;
  expectedFields.forEach((expected, index) => {
    const dbField = fieldMetadata[index];
    const match = dbField && dbField.field_name === expected.name && dbField.data_source === expected.source;
    console.log(`  ${index + 1}. ${expected.name} [${expected.source}]: ${match ? '✅' : '❌'}`);
    if (!match) allMatch = false;
  });
  
  // 4. 检查不应该存在的字段
  console.log('\n🔍 检查问题字段:');
  const problemFields = dataFields.filter(col => col.name === '_' || col.name === '—');
  if (problemFields.length === 0) {
    console.log('  ✅ 没有发现问题字段("_"或"—")');
  } else {
    console.log('  ❌ 发现问题字段:');
    problemFields.forEach(field => console.log(`    - ${field.name}`));
    allMatch = false;
  }
  
  // 5. 最终结果
  console.log('\n🎯 修复验证结果:');
  console.log(`  字段数量: ${dataFields.length === 6 ? '✅' : '❌'} (应为6个，实际${dataFields.length}个)`);
  console.log(`  字段名称: ${allMatch ? '✅' : '❌'} (与Excel定义匹配)`);
  console.log(`  字段元数据: ${fieldMetadata.length === 6 ? '✅' : '❌'} (应为6条，实际${fieldMetadata.length}条)`);
  
  if (allMatch && dataFields.length === 6 && fieldMetadata.length === 6) {
    console.log('\n🎉 table_c14修复完全成功！');
    console.log('   ✅ 所有字段与Excel定义完全匹配');
    console.log('   ✅ 字段元数据正确');
    console.log('   ✅ 数据来源权限正确');
  } else {
    console.log('\n⚠️  发现问题，需要进一步检查');
  }
  
} catch (error) {
  console.error('❌ 验证失败:', error.message);
} finally {
  db.close();
}